---
sidebar_label: Enum type fields
sidebar_position: 7
description: Use enums in Hasura with Postgres
keywords:
  - hasura
  - docs
  - postgres
  - schema
  - enum
---

import GraphiQLIDE from '@site/src/components/GraphiQLIDE';
import Thumbnail from '@site/src/components/Thumbnail';
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';

# Postgres: Enum Type Fields

## Introduction

Enum type fields are restricted to a fixed set of allowed values.

## Enums in a database

In a relational database such as Postgres, an enum type field in a table can be defined in two ways:

### Using [native Postgres enum types](https://www.postgresql.org/docs/current/datatype-enum.html) {#pg-native-enum}

While the most obvious solution, native enum types have significant drawbacks: they are not easily mutable. New values
cannot be added to an enum inside a transaction (that is, `ALTER TYPE ... ADD VALUE` is not supported by transactional
DDL), and values cannot be removed from an enum at all without completely dropping and recreating it (which cannot be
done if the enum is in use by _any_ tables, views, or functions). Therefore, native enum types should only be used for
enums that are guaranteed to _never_ change, such as days of the week.

### Using [foreign-key references](https://www.postgresql.org/docs/current/tutorial-fk.html) to a single-column table {#pg-reference-table-enum}

This approach represents an enum using ordinary relational database concepts. The enum type is represented by a table,
and the values of the enum are rows in the table. Columns in other tables that use the enum are ordinary foreign-key
references to the enum table.

For enums with values that are dynamic and may require updates, such as a list of tags or user roles, this approach is
strongly recommended. Modifying an enum defined this way is easy: simply insert, update, or delete rows in the enum
table (and updates or deletes can even be cascaded to references, and they may be done within a transaction).

## Enums in the Hasura GraphQL Engine

Given the limitations of native Postgres enum types (as described [above](#pg-native-enum)), Hasura currently only
generates GraphQL enum types for enums defined using the [referenced tables](#pg-reference-table-enum) approach.

You may use native Postgres enum types in your database schema, but they will essentially be treated like text fields in
the generated GraphQL schema. Therefore, this guide focuses primarily on modeling an enum using a reference table, but
you may still use native Postgres enum types to help maintain data consistency in your database. You can always choose
to create a table with the values of a Postgres enum as shown in the
[section below](#pg-create-enum-table-from-pg-enum).

**Example:** Let’s say we have a database that tracks user information, and users may only have one of three specific
roles: user, moderator, or administrator. To represent that, we might have a `users` table with the following schema:

```sql
CREATE TABLE users (
  id serial PRIMARY KEY,
  name text NOT NULL,
  role text NOT NULL
);
```

Now we can insert some users into our database:

```sql
INSERT INTO users (name, role) VALUES
  ('Alyssa', 'administrator'),
  ('Ben', 'moderator'),
  ('Gerald', 'user');
```

This works alright, but it doesn’t prevent us from inserting nonsensical values for `role`, such as

```sql
INSERT INTO users (name, role) VALUES
  ('Hal', 'spaghetti');
```

which we certainly don’t want. Hence we should create an enum to restrict the allowed values.

### Creating an enum compatible table {#pg-create-enum-table}

To represent an enum, we’re going to create an \_<em>enum table</em>, which for Hasura’s purposes is any table that
meets the following restrictions:

1.  The table must have a single-column primary key of type `text`. The values of this column are the legal values of
    the enum, and they must all be
    [valid GraphQL enum value names](https://graphql.github.io/graphql-spec/June2018/#EnumValue).
2.  Optionally, the table may have a second column, also of type `text`, which will be used as a description of each
    value in the generated GraphQL schema.
3.  The table must not contain any other columns.
4.  The table must contain at least 1 row.

**For example**, to create an enum that represents our user roles, we would create the following table:

```sql
CREATE TABLE user_role (
  value text PRIMARY KEY,
  comment text
);

INSERT INTO user_role (value, comment) VALUES
  ('user', 'Ordinary users'),
  ('moderator', 'Users with the privilege to ban users'),
  ('administrator', 'Users with the privilege to set users’ roles');
```

<div id="pg-create-enum-table-from-pg-enum" />

:::info Creating an enum table from a native PG enum

You can create a table containing the values of a PG enum by executing the following SQL:

```sql
CREATE TABLE "<my_enum_table>" (value TEXT PRIMARY KEY);
INSERT INTO "<my_enum_table>" (value) (SELECT unnest(enum_range(NULL::"<my_enum>"))::text);
```

:::

Next, we need to tell Hasura that this table represents an enum.

### Setting a table as an enum table

Once we have a table which satisfies the conditions for an enum table as described [above](#pg-create-enum-table), we
need to tell Hasura that this table represents an enum.

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

Head to the `Data -> [table-name] -> Modify` tab in the Console and toggle the switch in the `Set table as enum`
section:

<Thumbnail src="/img/schema/enum-set.png" alt="Set table as enum" />

</TabItem>
<TabItem value="cli" label="CLI">
To set a table as an enum, change the `tables.yaml` file in the
`metadata` directory as follows:

```yaml {4}
- table:
    schema: public
    name: user_role
  is_enum: true
```

Apply the Metadata by running:

```bash
hasura metadata apply
```

</TabItem>
<TabItem value="api" label="API">
A table can be set as an enum via the following 2 methods:

1.  Passing `true` for the `is_enum` option of the
    [pg_track_table](/api-reference/metadata-api/table-view.mdx#metadata-pg-track-table) Metadata API while tracking a
    table:

    ```http {11}
    POST /v1/metadata HTTP/1.1
    Content-Type: application/json
    X-Hasura-Role: admin

    {
      "type": "pg_track_table",
      "args": {
        "source": "<db_name>",
        "schema": "public",
        "name": "user_role",
        "is_enum": true
      }
    }
    ```

2.  Using the [pg_set_table_is_enum](/api-reference/metadata-api/table-view.mdx#metadata-pg-set-table-is-enum) metadata
    API to change whether or not an already-tracked table should be used as an enum:

    ```http
    POST /v1/metadata HTTP/1.1
    Content-Type: application/json
    X-Hasura-Role: admin

    {
      "type": "pg_set_table_is_enum",
      "args": {
        "source": "<db_name>",
        "table": {
          "schema": "public",
          "name": "user_role"
        },
        "is_enum": true
      }
    }
    ```

</TabItem>
</Tabs>

### Using an enum table

To set a field of a table as an enum in the GraphQL schema, we need to set a reference from it to the enum table via a
foreign key.

**For example**, to update our `users` table to reference the `user_role` enum table:

```sql
ALTER TABLE users ADD CONSTRAINT
  users_role_fkey FOREIGN KEY (role) REFERENCES user_role;
```

### Making queries using enum values

Once a table has been tracked as an enum, the GraphQL schema will be updated to expose the values of the table as
GraphQL enum values i.e. only the exposed values will be permitted for all fields referencing to it.

**For example**, the `role` column of the `users` table only permits the values in the `user_role` table:

```graphql
type users {
  id: Int!
  name: String!
  role: user_role_enum!
}

enum user_role_enum {
  "Users with the privilege to set users’ roles"
  administrator

  "Users with the privilege to ban users"
  moderator

  "Ordinary users"
  user
}
```

When making queries that filter on the `role` column, use the name of the enum value directly rather than providing a
string:

<GraphiQLIDE
  query={`{
  users(
    where: {
      role: {_eq: administrator}
    }
  ) {
    id
    name
  }
}`}
  response={`{
  "data": {
    "users": [
      {
        "id": 1,
        "name": "Alyssa"
      }
    ]
  }
}`}
/>

### Enums and Migrations

As enum tables have a requirement to contain at least 1 row, it is necessary to have a migration which inserts values
into an enum table. Otherwise while applying Migrations an error would be thrown while trying to set the table as an
enum.

The migration which inserts values into an enum table needs to be between the migration creating the table and the
migration setting it as an enum.

This can be achieved via the Console by performing the following steps while setting up an enum table:

1.  Create the enum table
2.  Use the `RawSQL` tab of the Console to insert the enum values into the table and mark the insert as a migration
3.  Set the table as an enum

You can also
[manually create migration files](/migrations-metadata-seeds/manage-migrations.mdx#create-manual-migrations) to achieve
this.

### Current limitations

Currently, Hasura does not automatically detect changes to the contents of enum tables, so the GraphQL schema will only
be updated after [manually reloading metadata](/migrations-metadata-seeds/manage-metadata.mdx#reload-metadata-manual)
after inserting, updating, or deleting rows from an enum table.
